﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Windows.Forms;

namespace VietKinsNutrition.Data
{
    public class ClsTblPhieutienanDAL
    {
        ClsBindingBLL ClsBin = new ClsBindingBLL();
        //ClsConnection ClsCnn = new ClsConnection();
        //
        // check 
        //[]
        public bool CheckBienlai(int NamhocId, int ThanghocId, int LophocId, int NamtrehocId, string Sobienlai)
        {
            bool returnValue = false;
            string NamePrc = QueryString.SP_PHIEUTIENAN_SELECTBYLOPHOCIDNAMHOCIDTHANGHOCIDNAMTREHOCIDSOBIENLAI.Replace("@NamhocId", NamhocId.ToString()).Replace("@ThanghocId", ThanghocId.ToString()).Replace("@LophocId", LophocId.ToString()).Replace("@NamtrehocId", NamtrehocId.ToString()).Replace("@Sobienlai", "'" + Sobienlai + "'");
            string NameTable = "TblPhieutienan";
            DataSet Dset = new DataSet();
            OleDbCommand Cmm = new OleDbCommand();

            OleDbDataAdapter Dap;
            Cmm.Connection = ClsConnection.OleDBConnect();
            Cmm.CommandText = NamePrc;
            //Cmm.CommandType = CommandType.StoredProcedure;
            try
            {
                if (Cmm.Connection.State != ConnectionState.Open) Cmm.Connection.Open(); Cmm.ExecuteNonQuery();
            }
            catch (Exception Ex)
            {
            }
            finally
            {
                Cmm.Connection.Close();
            }
            Dap = new OleDbDataAdapter(Cmm);

            Dap.Fill(Dset);
            // add tongcong ở đây
            if (Dset.Tables[0].Rows.Count > 0)
            {
                returnValue = true;
            }
            else returnValue = false;
            return returnValue;

        }
        public DataSet SelectAllBySobienlai(string Sobienlai)
        {
            string NamePrc = QueryString.SPROC_TBLPHIEUTIENAN_SELECTALLBYSOBIENLAI;
            string NameTable = "TblPhieutienan";
            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();
            NewTable.TableName = NameTable;

            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            Dset.Tables.Add(NewTable);
            OleDbCommand Cmm = new OleDbCommand();
            Cmm.Parameters.Add(new OleDbParameter("@Sobienlai", Sobienlai));
            OleDbDataAdapter Dap;
            Cmm.Connection = ClsConnection.OleDBConnect();
            Cmm.CommandText = NamePrc;
            //Cmm.CommandType = CommandType.StoredProcedure;
            try
            {
                if (Cmm.Connection.State != ConnectionState.Open) Cmm.Connection.Open(); Cmm.ExecuteNonQuery();
            }
            catch (Exception Ex)
            {
            }
            finally
            {
                Cmm.Connection.Close();
            }
            Dap = new OleDbDataAdapter(Cmm);

            Dap.Fill(Dset.Tables[NameTable]);
            // add tongcong ở đây
            BindingSource Binding = new BindingSource(Dset, NameTable);
            return Dset;
        }
        //[]
        public DataSet Find_Dangkyphieutienan(string Hovaten, string Tenlop, string Tenthuonggoi, string UutienId, string Noio, string Gioitinh, int Varvalue, int ThanghocId, int NamhocId)
        {
            if (Gioitinh.Trim() == "n")
            { Gioitinh = ""; }
            if (Varvalue == 0 && Tenlop == "")
            {
                QueryString.SP_PHIEUTIENAN_FINDBYLOPHOCIDNAMHOCIDTHANGHOCID_ = "  SELECT     Tbl2.Hotendem, Tbl2.Ten, Tbl2.Hovaten, Tbl2.Tenthuonggoi, Tbl2.Ngaysinh, Tbl2.Gioitinh, Tbl2.Noiohiennay, Tbl2.Sodt, Tbl2.UutienId, Tbl2.Hotenbo, " +
  " Tbl2.Nghenghiepbo, Tbl2.Hotenme, Tbl2.Nghengheme, Tbl2.Ghichu, Tbl2.Tinhtrang, Tbl2.NamtrehocId, Tbl2.Id, Tbl2.NamhocId " +
" FROM (SELECT        TblTre.Id, TblTre.Hotendem, TblTre.Ten, TblTre.Hovaten, TblTre.Tenthuonggoi, TblTre.Ngaysinh, " +
" TblTre.Noiohiennay, TblTre.Gioitinh, TblTre.Sodt, TblTre.UutienId, TblTre.Hotenbo, TblTre.Nghenghiepbo, " +
" TblTre.Hotenme, TblTre.Nghengheme, TblTre.Ghichu, TblTre.Tinhtrang, TblNamtrehoc.Id AS NamtrehocId, " +
" TblPhieutienan.ThanghocId, TblNamtrehoc.NamhocId FROM     (( (  TblTre INNER JOIN TblDm_Uutien ON TblDm_Uutien.Id = TblTre.UutienId ) " +
" INNER JOIN  TblNamtrehoc ON TblTre.Id = TblNamtrehoc.TreId ) INNER JOIN TblPhieutienan ON TblNamtrehoc.Id = TblPhieutienan.NamtrehocId ) " +
" INNER JOIN TblLop ON TblNamtrehoc.LophocId = TblLop.Id " +
" WHERE (TblTre.Tinhtrang = 0) AND (TblTre.Hovaten LIKE '%' + @Hotendem + '%') AND (TblTre.Tenthuonggoi LIKE '%' + @Tenthuonggoi + '%') AND " +
" (TblTre.Noiohiennay LIKE '%' + @Noiohiennay + '%') AND (TblTre.Gioitinh LIKE '%' + @Gioitinh + '%') AND " +
" (TblPhieutienan.ThanghocId = @ThanghocId) AND (TblLop.Tenlop LIKE '%' + @Tenlop + '%') ) " +
  " AS Tbl1 RIGHT OUTER JOIN ( " +
" SELECT   TblTre_1.Id, TblTre_1.Hotendem, TblTre_1.Ten, TblTre_1.Hovaten, TblTre_1.Tenthuonggoi, TblTre_1.Ngaysinh, TblTre_1.Noiohiennay, TblTre_1.Gioitinh, TblTre_1.Sodt, TblTre_1.UutienId, TblTre_1.Hotenbo, TblTre_1.Nghenghiepbo, TblTre_1.Hotenme, TblTre_1.Nghengheme, TblTre_1.Ghichu, TblTre_1.Tinhtrang, TblNamtrehoc_1.Id AS NamtrehocId, TblNamtrehoc_1.NamhocId " +
" FROM      ( ( TblTre AS TblTre_1 INNER JOIN TblDm_Uutien AS TblDm_Uutien_1 ON TblDm_Uutien_1.Id = TblTre_1.UutienId ) " +
 " INNER JOIN TblNamtrehoc AS TblNamtrehoc_1 ON TblTre_1.Id = TblNamtrehoc_1.TreId)" +
"  INNER JOIN TblLop AS TblLop_1 ON TblNamtrehoc_1.LophocId = TblLop_1.Id " +
"  WHERE     (TblTre_1.Tinhtrang = 0) AND (TblTre_1.Hovaten LIKE '%' + @Hotendem + '%') AND (TblTre_1.Tenthuonggoi LIKE '%' + @Tenthuonggoi + '%') AND  " +
 "  (TblTre_1.Noiohiennay LIKE '%' + @Noiohiennay + '%') AND (TblTre_1.Gioitinh LIKE '%' + @Gioitinh + '%') AND  (TblLop_1.Tenlop LIKE '%' + @Tenlop + '%') " +
" )AS Tbl2 ON Tbl1.NamtrehocId = Tbl2.NamtrehocId WHERE     (Tbl1.Id IS NULL and Tbl2.NamhocId =@NamhocId)";
            }

            if (Varvalue == 1 && Tenlop == "")
            {
                QueryString.SP_PHIEUTIENAN_FINDBYLOPHOCIDNAMHOCIDTHANGHOCID_ = "SELECT     Tbl1.Id , Tbl1.Ten, Tbl1.Hotendem, Tbl1.Hovaten, Tbl1.Tenthuonggoi, Tbl1.Ngaysinh, Tbl1.Gioitinh, Tbl1.Noiohiennay, Tbl1.Sodt, Tbl1.UutienId, " +
  " Tbl1.Hotenbo, Tbl1.Nghenghiepbo, Tbl1.Hotenme, Tbl1.Nghengheme, Tbl1.Ghichu, Tbl1.Tinhtrang, Tbl1.NamtrehocId, Tbl1.NamhocId " +
" FROM   ( SELECT     TblTre.Id, TblTre.Hotendem, TblTre.Ten, TblTre.Hovaten, TblTre.Tenthuonggoi, TblTre.Ngaysinh,  " +
  " TblTre.Noiohiennay, TblTre.Gioitinh, TblTre.Sodt, TblTre.UutienId, TblTre.Hotenbo, TblTre.Nghenghiepbo, " +
  " TblTre.Hotenme, TblTre.Nghengheme, TblTre.Ghichu, TblTre.Tinhtrang, TblNamtrehoc.Id AS NamtrehocId, " +
  " TblPhieutienan.ThanghocId, TblNamtrehoc.NamhocId FROM      (( ( TblTre INNER JOIN  TblDm_Uutien ON TblDm_Uutien.Id = TblTre.UutienId)" +
 " INNER JOIN   TblNamtrehoc ON TblTre.Id = TblNamtrehoc.TreId )INNER JOIN   TblPhieutienan ON TblNamtrehoc.Id = TblPhieutienan.NamtrehocId )" +
" INNER JOIN   TblLop ON TblNamtrehoc.LophocId = TblLop.Id " +
" WHERE     (TblTre.Tinhtrang = 0) AND (TblTre.Hovaten LIKE '%' + @Hotendem + '%') AND (TblTre.Tenthuonggoi LIKE '%' + @Tenthuonggoi + '%') AND " +
  " (TblTre.Noiohiennay LIKE '%' + @Noiohiennay + '%') AND (TblTre.Gioitinh LIKE '%' + @Gioitinh + '%') AND " +
  " (TblPhieutienan.ThanghocId = @ThanghocId) AND (TblLop.Tenlop LIKE '%' + @Tenlop + '%') " +
" ) AS Tbl1 LEFT OUTER JOIN ( SELECT      TblTre_1.Id, TblTre_1.Hotendem, TblTre_1.Ten, TblTre_1.Hovaten, TblTre_1.Tenthuonggoi, TblTre_1.Ngaysinh,  " +
  " TblTre_1.Noiohiennay, TblTre_1.Gioitinh, TblTre_1.Sodt, TblTre_1.UutienId, TblTre_1.Hotenbo, TblTre_1.Nghenghiepbo, TblTre_1.Hotenme,  " +
  " TblTre_1.Nghengheme, TblTre_1.Ghichu, TblTre_1.Tinhtrang, TblNamtrehoc_1.Id AS NamtrehocId, TblNamtrehoc_1.NamhocId " +
" FROM       ( ( TblTre AS TblTre_1 INNER JOIN   TblDm_Uutien AS TblDm_Uutien_1 ON TblDm_Uutien_1.Id = TblTre_1.UutienId )" +
" INNER JOIN   TblNamtrehoc AS TblNamtrehoc_1 ON TblTre_1.Id = TblNamtrehoc_1.TreId ) " +
" INNER JOIN   TblLop AS TblLop_1 ON TblNamtrehoc_1.LophocId = TblLop_1.Id " +
" WHERE     (TblTre_1.Tinhtrang = 0) AND (TblTre_1.Hovaten LIKE '%' + @Hotendem + '%') AND (TblTre_1.Tenthuonggoi LIKE '%' + @Tenthuonggoi + '%') AND " +
  " (TblTre_1.Noiohiennay LIKE '%' + @Noiohiennay + '%') AND (TblTre_1.Gioitinh LIKE '%' + @Gioitinh + '%') AND  " +
  " (TblLop_1.Tenlop LIKE '%' + @Tenlop + '%')) AS Tbl2 ON Tbl1.NamtrehocId = Tbl2.NamtrehocId where (Tbl1.NamhocId =@NamhocId) ";
            }
            if (Varvalue == -1 && Tenlop == "")
            {
                QueryString.SP_PHIEUTIENAN_FINDBYLOPHOCIDNAMHOCIDTHANGHOCID_ = "SELECT     Tbl2.Hotendem, Tbl2.Ten, Tbl2.Hovaten, Tbl2.Tenthuonggoi, Tbl2.Ngaysinh, Tbl2.Gioitinh, Tbl2.Noiohiennay, Tbl2.Sodt, Tbl2.UutienId, Tbl2.Hotenbo, " +
  " Tbl2.Nghenghiepbo, Tbl2.Hotenme, Tbl2.Nghengheme, Tbl2.Ghichu, Tbl2.Tinhtrang, Tbl2.NamtrehocId, Tbl2.Id, Tbl2.NamhocId" +
" FROM         (SELECT     TblTre.Id, TblTre.Hotendem, TblTre.Ten, TblTre.Hovaten, TblTre.Tenthuonggoi, TblTre.Ngaysinh,  " +
  " TblTre.Noiohiennay, TblTre.Gioitinh, TblTre.Sodt, TblTre.UutienId, TblTre.Hotenbo, TblTre.Nghenghiepbo, " +
  " TblTre.Hotenme, TblTre.Nghengheme, TblTre.Ghichu, TblTre.Tinhtrang, TblNamtrehoc.Id AS NamtrehocId,  " +
  " TblPhieutienan.ThanghocId, TblNamtrehoc.NamhocId" +
" FROM    (((     TblTre INNER JOIN   TblDm_Uutien ON TblDm_Uutien.Id = TblTre.UutienId) INNER JOIN " +
  " TblNamtrehoc ON TblTre.Id = TblNamtrehoc.TreId) INNER JOIN " +
  " TblPhieutienan ON TblNamtrehoc.Id = TblPhieutienan.NamtrehocId )" +
" INNER JOIN   TblLop ON TblNamtrehoc.LophocId = TblLop.Id " +
" WHERE     (TblTre.Tinhtrang = 0) AND (TblTre.Hovaten LIKE '%' + @Hotendem + '%') AND (TblTre.Tenthuonggoi LIKE '%' + @Tenthuonggoi + '%') AND " +
  " (TblTre.Noiohiennay LIKE '%' + @Noiohiennay + '%') AND (TblTre.Gioitinh LIKE '%' + @Gioitinh + '%') AND  " +
  " (TblPhieutienan.ThanghocId = @ThanghocId) AND (TblLop.Tenlop LIKE '%' + @Tenlop + '%')) AS Tbl1 RIGHT OUTER JOIN " +
 " (SELECT     TblTre_1.Id, TblTre_1.Hotendem, TblTre_1.Ten, TblTre_1.Hovaten, TblTre_1.Tenthuonggoi, TblTre_1.Ngaysinh, " +
  " TblTre_1.Noiohiennay, TblTre_1.Gioitinh, TblTre_1.Sodt, TblTre_1.UutienId, TblTre_1.Hotenbo, TblTre_1.Nghenghiepbo, TblTre_1.Hotenme,  " +
  " TblTre_1.Nghengheme, TblTre_1.Ghichu, TblTre_1.Tinhtrang, TblNamtrehoc_1.Id AS NamtrehocId, TblNamtrehoc_1.NamhocId" +
" FROM        (( TblTre AS TblTre_1 INNER JOIN TblDm_Uutien AS TblDm_Uutien_1 ON TblDm_Uutien_1.Id = TblTre_1.UutienId )" +
" INNER JOIN   TblNamtrehoc AS TblNamtrehoc_1 ON TblTre_1.Id = TblNamtrehoc_1.TreId)  " +
" INNER JOIN   TblLop AS TblLop_1 ON TblNamtrehoc_1.LophocId = TblLop_1.Id " +
" WHERE     (TblTre_1.Tinhtrang = 0) AND (TblTre_1.Hovaten LIKE '%' + @Hotendem + '%') AND (TblTre_1.Tenthuonggoi LIKE '%' + @Tenthuonggoi + '%') AND " +
  " (TblTre_1.Noiohiennay LIKE '%' + @Noiohiennay + '%') AND (TblTre_1.Gioitinh LIKE '%' + @Gioitinh + '%') AND " +
  " (TblLop_1.Tenlop LIKE '%' + @Tenlop + '')) AS Tbl2 ON Tbl1.NamtrehocId = Tbl2.NamtrehocId where (Tbl2.NamhocId =@NamhocId)";
            }
            if (Varvalue == 0 && Tenlop != "")
            {
                QueryString.SP_PHIEUTIENAN_FINDBYLOPHOCIDNAMHOCIDTHANGHOCID_ = "SELECT     Tbl2.Hotendem, Tbl2.Ten, Tbl2.Hovaten, Tbl2.Tenthuonggoi, Tbl2.Ngaysinh, Tbl2.Gioitinh, Tbl2.Noiohiennay, Tbl2.Sodt, Tbl2.UutienId, Tbl2.Hotenbo, " +
                      " Tbl2.Nghenghiepbo, Tbl2.Hotenme, Tbl2.Nghengheme, Tbl2.Ghichu, Tbl2.Tinhtrang, Tbl2.NamtrehocId, Tbl2.Id, Tbl2.NamhocId " +
" FROM         (SELECT     TblTre.Id, TblTre.Hotendem, TblTre.Ten, TblTre.Hovaten, TblTre.Tenthuonggoi, TblTre.Ngaysinh,  " +
                      " TblTre.Noiohiennay, TblTre.Gioitinh, TblTre.Sodt, TblTre.UutienId, TblTre.Hotenbo, TblTre.Nghenghiepbo, " +
                      " TblTre.Hotenme, TblTre.Nghengheme, TblTre.Ghichu, TblTre.Tinhtrang, TblNamtrehoc.Id AS NamtrehocId, " +
                      " TblPhieutienan.ThanghocId, TblNamtrehoc.NamhocId FROM     (( (   TblTre INNER JOIN    TblDm_Uutien ON TblDm_Uutien.Id = TblTre.UutienId) INNER JOIN " +
                      " TblNamtrehoc ON TblTre.Id = TblNamtrehoc.TreId) INNER JOIN TblPhieutienan ON TblNamtrehoc.Id = TblPhieutienan.NamtrehocId) " +
" INNER JOIN         TblLop ON TblNamtrehoc.LophocId = TblLop.Id WHERE     (TblTre.Tinhtrang = 0) AND (TblTre.Hovaten LIKE '%' + @Hotendem + '%') AND (TblTre.Tenthuonggoi LIKE '%' + @Tenthuonggoi + '%') AND  " +
  " (TblTre.Noiohiennay LIKE '%' + @Noiohiennay + '%') AND (TblTre.Gioitinh LIKE '%' + @Gioitinh + '%') AND (TblLop.Tenlop = @Tenlop) AND " +
                      " (TblPhieutienan.ThanghocId = @ThanghocId))    AS Tbl1 RIGHT OUTER JOIN " +
              " (SELECT      TblTre_1.Id, TblTre_1.Hotendem, TblTre_1.Ten, TblTre_1.Hovaten, TblTre_1.Tenthuonggoi, TblTre_1.Ngaysinh, " +
                      " TblTre_1.Noiohiennay, TblTre_1.Gioitinh, TblTre_1.Sodt, TblTre_1.UutienId, TblTre_1.Hotenbo, TblTre_1.Nghenghiepbo, TblTre_1.Hotenme,  " +
                      " TblTre_1.Nghengheme, TblTre_1.Ghichu, TblTre_1.Tinhtrang, TblNamtrehoc_1.Id AS NamtrehocId, TblNamtrehoc_1.NamhocId " +
" FROM      (  ( TblTre AS TblTre_1 INNER JOIN    TblDm_Uutien AS TblDm_Uutien_1 ON TblDm_Uutien_1.Id = TblTre_1.UutienId) " +
 " INNER JOIN        TblNamtrehoc AS TblNamtrehoc_1 ON TblTre_1.Id = TblNamtrehoc_1.TreId) " +
 " INNER JOIN           TblLop AS TblLop_1 ON TblNamtrehoc_1.LophocId = TblLop_1.Id " +
 " WHERE     (TblTre_1.Tinhtrang = 0) AND (TblTre_1.Hovaten LIKE '%' + @Hotendem + '%') AND (TblTre_1.Tenthuonggoi LIKE '%' + @Tenthuonggoi + '%') AND " +
                       " (TblTre_1.Noiohiennay LIKE '%' + @Noiohiennay + '%') AND (TblTre_1.Gioitinh LIKE '%' + @Gioitinh + '%') AND (TblLop_1.Tenlop = @Tenlop)) AS Tbl2 ON  " +
                      " Tbl1.NamtrehocId = Tbl2.NamtrehocId WHERE     (Tbl1.Id IS NULL and Tbl2.NamhocId =@NamhocId)";
            }
            if (Varvalue == 1 && Tenlop != "")
            {
                QueryString.SP_PHIEUTIENAN_FINDBYLOPHOCIDNAMHOCIDTHANGHOCID_ = "SELECT     Tbl1.Id , Tbl1.Ten, Tbl1.Hotendem, Tbl1.Hovaten, Tbl1.Tenthuonggoi, Tbl1.Ngaysinh, Tbl1.Gioitinh, Tbl1.Noiohiennay, Tbl1.Sodt, Tbl1.UutienId, " +
                       " Tbl1.Hotenbo, Tbl1.Nghenghiepbo, Tbl1.Hotenme, Tbl1.Nghengheme, Tbl1.Ghichu, Tbl1.Tinhtrang, Tbl1.NamtrehocId, Tbl1.NamhocId " +
" FROM    (SELECT     TblTre.Id, TblTre.Hotendem, TblTre.Ten, TblTre.Hovaten, TblTre.Tenthuonggoi, TblTre.Ngaysinh, " +
                      " TblTre.Noiohiennay, TblTre.Gioitinh, TblTre.Sodt, TblTre.UutienId, TblTre.Hotenbo, TblTre.Nghenghiepbo, " +
                      " TblTre.Hotenme, TblTre.Nghengheme, TblTre.Ghichu, TblTre.Tinhtrang, TblNamtrehoc.Id AS NamtrehocId, " +
                      " TblPhieutienan.ThanghocId, TblNamtrehoc.NamhocId " +
" FROM       (( ( TblTre INNER JOIN                       TblDm_Uutien ON TblDm_Uutien.Id = TblTre.UutienId) " +
 " INNER JOIN                       TblNamtrehoc ON TblTre.Id = TblNamtrehoc.TreId ) " +
" INNER JOIN                       TblPhieutienan ON TblNamtrehoc.Id = TblPhieutienan.NamtrehocId ) " +
" INNER JOIN                      TblLop ON TblNamtrehoc.LophocId = TblLop.Id " +
"  WHERE     (TblTre.Tinhtrang = 0) AND (TblTre.Hovaten LIKE '%' + @Hotendem + '%') AND (TblTre.Tenthuonggoi LIKE '%' + @Tenthuonggoi + '%') AND  " +
                      " (TblTre.Noiohiennay LIKE '%' + @Noiohiennay + '%') AND (TblTre.Gioitinh LIKE '%' + @Gioitinh + '%') AND  " +
                      " (TblPhieutienan.ThanghocId = @ThanghocId) AND (TblLop.Tenlop = @Tenlop)) AS Tbl1 LEFT OUTER JOIN " +
                          " (SELECT      TblTre_1.Id, TblTre_1.Hotendem, TblTre_1.Ten, TblTre_1.Hovaten, TblTre_1.Tenthuonggoi, TblTre_1.Ngaysinh,  " +
                      " TblTre_1.Noiohiennay, TblTre_1.Gioitinh, TblTre_1.Sodt, TblTre_1.UutienId, TblTre_1.Hotenbo, TblTre_1.Nghenghiepbo, TblTre_1.Hotenme, " +
                      " TblTre_1.Nghengheme, TblTre_1.Ghichu, TblTre_1.Tinhtrang, TblNamtrehoc_1.Id AS NamtrehocId, TblNamtrehoc_1.NamhocId " +
" FROM      ((   TblTre AS TblTre_1 INNER JOIN            TblDm_Uutien AS TblDm_Uutien_1 ON TblDm_Uutien_1.Id = TblTre_1.UutienId) INNER JOIN " +
                      " TblNamtrehoc AS TblNamtrehoc_1 ON TblTre_1.Id = TblNamtrehoc_1.TreId) INNER JOIN " +
                      " TblLop AS TblLop_1 ON TblNamtrehoc_1.LophocId = TblLop_1.Id " +
" WHERE     (TblTre_1.Tinhtrang = 0) AND (TblTre_1.Hovaten LIKE '%' + @Hotendem + '%') AND (TblTre_1.Tenthuonggoi LIKE '%' + @Tenthuonggoi + '%') AND  " +
                      " (TblTre_1.Noiohiennay LIKE '%' + @Noiohiennay + '%') AND (TblTre_1.Gioitinh LIKE '%' + @Gioitinh + '%') AND (TblLop_1.Tenlop = @Tenlop) " +
" ) AS Tbl2 ON Tbl1.NamtrehocId = Tbl2.NamtrehocId where (Tbl1.NamhocId =@NamhocId)";
            }

            if (Varvalue == -1 && Tenlop != "")
            {
                QueryString.SP_PHIEUTIENAN_FINDBYLOPHOCIDNAMHOCIDTHANGHOCID_ = " SELECT     Tbl2.Hotendem, Tbl2.Ten, Tbl2.Hovaten, Tbl2.Tenthuonggoi, Tbl2.Ngaysinh, Tbl2.Gioitinh, Tbl2.Noiohiennay, Tbl2.Sodt, Tbl2.UutienId, Tbl2.Hotenbo, " +
                      " Tbl2.Nghenghiepbo, Tbl2.Hotenme, Tbl2.Nghengheme, Tbl2.Ghichu, Tbl2.Tinhtrang, Tbl2.NamtrehocId, Tbl2.Id, Tbl2.NamhocId " +
" FROM         (SELECT      TblTre.Id, TblTre.Hotendem, TblTre.Ten, TblTre.Hovaten, TblTre.Tenthuonggoi, TblTre.Ngaysinh, " +
                      " TblTre.Noiohiennay, TblTre.Gioitinh, TblTre.Sodt, TblTre.UutienId, TblTre.Hotenbo, TblTre.Nghenghiepbo, " +
                      " TblTre.Hotenme, TblTre.Nghengheme, TblTre.Ghichu, TblTre.Tinhtrang, TblNamtrehoc.Id AS NamtrehocId,  " +
                      " TblPhieutienan.ThanghocId, TblNamtrehoc.NamhocId " +
"FROM       ((( TblTre INNER JOIN                      TblDm_Uutien ON TblDm_Uutien.Id = TblTre.UutienId) INNER JOIN " +
                      " TblNamtrehoc ON TblTre.Id = TblNamtrehoc.TreId) INNER JOIN " +
                      " TblPhieutienan ON TblNamtrehoc.Id = TblPhieutienan.NamtrehocId) INNER JOIN " +
                      " TblLop ON TblNamtrehoc.LophocId = TblLop.Id " +
" WHERE     (TblTre.Tinhtrang = 0) AND (TblTre.Hovaten LIKE '%' + @Hotendem + '%') AND (TblTre.Tenthuonggoi LIKE '%' + @Tenthuonggoi + '%') AND  " +
                      " (TblTre.Noiohiennay LIKE '%' + @Noiohiennay + '%') AND (TblTre.Gioitinh LIKE '%' + @Gioitinh + '%') AND (TblLop.Tenlop = @Tenlop) AND  " +
                      " (TblPhieutienan.ThanghocId = @ThanghocId) ) AS Tbl1 RIGHT OUTER JOIN ( " +
" SELECT      TblTre_1.Id, TblTre_1.Hotendem, TblTre_1.Ten, TblTre_1.Hovaten, TblTre_1.Tenthuonggoi, TblTre_1.Ngaysinh, " +
                      " TblTre_1.Noiohiennay, TblTre_1.Gioitinh, TblTre_1.Sodt, TblTre_1.UutienId, TblTre_1.Hotenbo, TblTre_1.Nghenghiepbo, TblTre_1.Hotenme, " +
                      " TblTre_1.Nghengheme, TblTre_1.Ghichu, TblTre_1.Tinhtrang, TblNamtrehoc_1.Id AS NamtrehocId, TblNamtrehoc_1.NamhocId " +
" FROM       ((  TblTre AS TblTre_1 INNER JOIN " +
                      " TblDm_Uutien AS TblDm_Uutien_1 ON TblDm_Uutien_1.Id = TblTre_1.UutienId) INNER JOIN " +
                       " TblNamtrehoc AS TblNamtrehoc_1 ON TblTre_1.Id = TblNamtrehoc_1.TreId) INNER JOIN " +
                      " TblLop AS TblLop_1 ON TblNamtrehoc_1.LophocId = TblLop_1.Id " +
" WHERE     (TblTre_1.Tinhtrang = 0) AND (TblTre_1.Hovaten LIKE '%' + @Hotendem + '%') AND (TblTre_1.Tenthuonggoi LIKE '%' + @Tenthuonggoi + '%') AND  " +
                      " (TblTre_1.Noiohiennay LIKE '%' + @Noiohiennay + '%') AND (TblTre_1.Gioitinh LIKE '%' + @Gioitinh + '%') AND (TblLop_1.Tenlop = @Tenlop) " +
" ) AS Tbl2 ON Tbl1.NamtrehocId = Tbl2.NamtrehocId where (Tbl2.NamhocId =@NamhocId)  ";
            }
            //        string NamePrc = QueryString.SP_PHIEUTIENAN_FINDBYLOPHOCIDNAMHOCIDTHANGHOCID_.Replace("@Hotendem", "'" + Hovaten + "'").Replace("@Tenthuonggoi", "'" + Tenthuonggoi + "'").Replace("@Noiohiennay", Noio).Replace("@Gioitinh", "'" + Gioitinh + "'").Replace("@Tenlop", "'" + Tenlop + "'").Replace("@UutienId", "'" + UutienId.ToString()+"'").Replace("@ThanghocId", ThanghocId.ToString()).Replace("@Varvalue", Varvalue.ToString()).Replace("@NamhocId", NamhocId.ToString());
            string NamePrc = QueryString.SP_PHIEUTIENAN_FINDBYLOPHOCIDNAMHOCIDTHANGHOCID_;
            NamePrc = NamePrc.Replace("'", "\"");
            NamePrc = NamePrc.Replace("@Hotendem", "\"" + Hovaten + "\"").Replace("@Tenthuonggoi", "\"" + Tenthuonggoi + "\"").Replace("@Noiohiennay", "\"" + Noio + "\"").Replace("@Gioitinh", "\"" + Gioitinh + "\"").Replace("@Tenlop", "\"" + Tenlop + "\"").Replace("@UutienId", "\"" + UutienId.ToString() + "\"").Replace("@ThanghocId", ThanghocId.ToString()).Replace("@Varvalue", Varvalue.ToString()).Replace("@NamhocId", NamhocId.ToString());

            string NameTable = "TblPhieutienan";
            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();
            NewTable.TableName = NameTable;

            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            Dset.Tables.Add(NewTable);
            OleDbCommand Cmm = new OleDbCommand();

            OleDbDataAdapter Dap;
            Cmm.Connection = ClsConnection.OleDBConnect();
            Cmm.CommandText = NamePrc;
            //Cmm.CommandType = CommandType.StoredProcedure;
            try
            {
                if (Cmm.Connection.State != ConnectionState.Open) Cmm.Connection.Open(); Cmm.ExecuteNonQuery();
            }
            catch (Exception Ex)
            {
            }
            finally
            {
                Cmm.Connection.Close();
            }
            Dap = new OleDbDataAdapter(Cmm);

            Dap.Fill(Dset.Tables[0]);

            return Dset;
        }

        public DataSet Phieutienan(string Hotendem, string Ten, string Tenthuonggoi, string UutienId, string Noio, string Gioitinh, int LophocId, int ThanghocId)
        {
            string NamePrc = QueryString.SPROC_PHIEUTIENAN;
            string NameTable = "TblPhieutienan";
            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();
            NewTable.TableName = NameTable;

            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            Dset.Tables.Add(NewTable);
            OleDbCommand Cmm = new OleDbCommand();
            Cmm.Parameters.Add(new OleDbParameter("@LophocID", LophocId));
            Cmm.Parameters.Add(new OleDbParameter("@Hotendem", Hotendem));
            Cmm.Parameters.Add(new OleDbParameter("@Tenthuonggoi", Tenthuonggoi));
            Cmm.Parameters.Add(new OleDbParameter("@UutienId", UutienId));
            Cmm.Parameters.Add(new OleDbParameter("@Noiohiennay", Noio));
            Cmm.Parameters.Add(new OleDbParameter("@ThanghocId", ThanghocId));
            Cmm.Parameters.Add(new OleDbParameter("@Gioitinh", Gioitinh));
            OleDbDataAdapter Dap;
            Cmm.Connection = ClsConnection.OleDBConnect();
            Cmm.CommandText = NamePrc;
            //Cmm.CommandType = CommandType.StoredProcedure;
            try
            {
                if (Cmm.Connection.State != ConnectionState.Open) Cmm.Connection.Open(); Cmm.ExecuteNonQuery();
            }
            catch (Exception Ex)
            {
            }
            finally
            {
                Cmm.Connection.Close();
            }
            Dap = new OleDbDataAdapter(Cmm);

            Dap.Fill(Dset.Tables[0]);

            return Dset;
        }

        public BindingSource BingSoure_Find(string Tenhocsinh, int NamhocId, int ThanghocId, string Tenlop, string Gioitinh)
        {
            if (Tenlop.Trim().ToUpper() == "TẤT CẢ")
            {
                QueryString.SPROC_TBLPHIEUTIENAN_SELECTALLBYTHANG_LOP_GIOITINH = "SELECT     TblPhieutienan.Id, TblPhieutienan.Ngaymua, TblPhieutienan.Sobienlai, TblTre.Hovaten, TblNamtrehoc.LophocId, "
            + "  TblPhieutienan.NamtrehocId, TblPhieutienan.ThanghocId, TblLop.Tenlop, TblPhieutienan.Tongsophieumua,  "
            + "  TblPhieutienan.Dongia, TblPhieutienan.Tongsophieudoi "
            + " FROM ((TblPhieutienan INNER JOIN TblNamtrehoc ON TblPhieutienan.NamtrehocId = TblNamtrehoc.Id) INNER JOIN "
            + "        TblLop ON TblNamtrehoc.LophocId = TblLop.Id) INNER JOIN TblTre ON TblNamtrehoc.TreId = TblTre.Id "
            + " WHERE TblNamtrehoc.NamhocId = " + NamhocId + " AND TblPhieutienan.ThanghocId = " + ThanghocId
            + "   AND TblTre.Gioitinh LIKE '%" + Gioitinh + "%' AND TblTre.Hovaten LIKE \"%" + Tenhocsinh + "%\" ";

            }
            else
            {
                QueryString.SPROC_TBLPHIEUTIENAN_SELECTALLBYTHANG_LOP_GIOITINH = "SELECT     TblPhieutienan.Id, TblPhieutienan.Ngaymua, TblPhieutienan.Sobienlai, TblTre.Hovaten, TblNamtrehoc.LophocId, "
                 + "  TblPhieutienan.NamtrehocId, TblPhieutienan.ThanghocId, TblLop.Tenlop, TblPhieutienan.Tongsophieumua,  "
                 + "  TblPhieutienan.Dongia, TblPhieutienan.Tongsophieudoi "
                 + " FROM ((TblPhieutienan INNER JOIN TblNamtrehoc ON TblPhieutienan.NamtrehocId = TblNamtrehoc.Id) INNER JOIN "
                 + "        TblLop ON TblNamtrehoc.LophocId = TblLop.Id) INNER JOIN TblTre ON TblNamtrehoc.TreId = TblTre.Id "
                 + " WHERE TblNamtrehoc.NamhocId = " + NamhocId + " AND TblPhieutienan.ThanghocId = " + ThanghocId + " AND TblLop.Tenlop =\"" + Tenlop +
                 "\"   AND TblTre.Gioitinh LIKE '%" + Gioitinh + "%' AND TblTre.Hovaten LIKE \"%" + Tenhocsinh + "%\" ";
            }
            string NamePrc = QueryString.SPROC_TBLPHIEUTIENAN_SELECTALLBYTHANG_LOP_GIOITINH;
            string NameTable = "TblPhieutienan";
            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();
            NewTable.TableName = NameTable;

            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            Dset.Tables.Add(NewTable);
            OleDbCommand Cmm = new OleDbCommand();
            OleDbDataAdapter Dap;
            Cmm.Connection = ClsConnection.OleDBConnect();
            Cmm.CommandText = NamePrc;
            try
            {
                if (Cmm.Connection.State != ConnectionState.Open) Cmm.Connection.Open(); Cmm.ExecuteNonQuery();
            }
            catch (Exception Ex)
            {
            }
            finally
            {
                Cmm.Connection.Close();
            }
            Dap = new OleDbDataAdapter(Cmm);

            Dap.Fill(Dset.Tables[NameTable]);
            // add tongcong ở đây
            Dset.Tables[NameTable].Columns.Add("Tongcong", Dset.Tables[NameTable].Columns["Tongsophieumua"].DataType);
            foreach (DataRow Rw in Dset.Tables[NameTable].Rows)
            {
                Rw["Tongcong"] = int.Parse(Rw["Tongsophieumua"].ToString()) + int.Parse(Rw["Tongsophieudoi"].ToString());
            }
            BindingSource Binding = new BindingSource(Dset, NameTable);
            return Binding;
        }
        public BindingSource SelectAll(int NamhocId, int ThanghocId)
        {
            string NamePrc = QueryString.SPROC_TBLPHIEUTIENAN_SELECTALLBYNAMHOCID;
            string NameTable = "TblPhieutienan";
            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();
            NewTable.TableName = NameTable;

            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            Dset.Tables.Add(NewTable);
            OleDbCommand Cmm = new OleDbCommand();
            Cmm.Parameters.Add(new OleDbParameter("@NamhocId", NamhocId));
            Cmm.Parameters.Add(new OleDbParameter("@ThanghocId", ThanghocId));
            OleDbDataAdapter Dap;
            Cmm.Connection = ClsConnection.OleDBConnect();
            Cmm.CommandText = NamePrc;
            //Cmm.CommandType = CommandType.StoredProcedure;
            try
            {
                if (Cmm.Connection.State != ConnectionState.Open) Cmm.Connection.Open(); Cmm.ExecuteNonQuery();
            }
            catch (Exception Ex)
            {
            }
            finally
            {
                Cmm.Connection.Close();
            }
            Dap = new OleDbDataAdapter(Cmm);

            Dap.Fill(Dset.Tables[NameTable]);
            // add tongcong ở đây
            Dset.Tables[NameTable].Columns.Add("Tongcong", Dset.Tables[NameTable].Columns["Tongsophieumua"].DataType);
            foreach (DataRow Rw in Dset.Tables[NameTable].Rows)
            {
                Rw["Tongcong"] = int.Parse(Rw["Tongsophieumua"].ToString()) + int.Parse(Rw["Tongsophieudoi"].ToString());
            }
            BindingSource Binding = new BindingSource(Dset, NameTable);
            return Binding;
        }
        public void Insert(string Sobienlai, int NamtrehocId, DateTime Ngaymua, int ThanghocId, int Tongsophieumua, int Tongsophieudoi, float Dongia, string Ghichu)
        {
            string NamePrc = QueryString.SPROC_TBLPHIEUTIENAN_INSERT.Replace("@Sobienlai", "'" + Sobienlai + "'").Replace("@NamtrehocId", NamtrehocId.ToString()).Replace("@Ngaymua", "#" + Ngaymua.ToShortDateString() + "#").Replace("@ThanghocId", ThanghocId.ToString()).Replace("@Tongsophieumua", Tongsophieumua.ToString()).Replace("@Tongsophieudoi", Tongsophieudoi.ToString()).Replace("@Dongia", Dongia.ToString()).Replace("@Ghichu", "'" + Ghichu + "'");
            OleDbCommand Cmm = new OleDbCommand();
            ClsConnection.OleDbDAdpter(NamePrc, Cmm);
        }

        public void Edit(int Id,
            string Sobienlai, int NamtrehocId,
            DateTime Ngaymua,
            int ThanghocId,
            int Tongsophieumua, int Tongsophieudoi, float Dongia, string Ghichu)
        {
            string NamePrc = QueryString.SPROC_TBLPHIEUTIENAN_UPDATE.Replace("@Id", Id.ToString()).Replace("@Sobienlai", Sobienlai).Replace("@NamtrehocId", NamtrehocId.ToString()).Replace("@Ngaymua", "#" + Ngaymua.ToShortDateString() + "#").Replace("@ThanghocId", ThanghocId.ToString()).Replace("@Tongsophieumua", Tongsophieumua.ToString()).Replace("@Tongsophieudoi", Tongsophieudoi.ToString()).Replace("@Dongia", Dongia.ToString()).Replace("@Ghichu", Ghichu);
            OleDbCommand Cmm = new OleDbCommand();
            ClsConnection.OleDbDAdpter(NamePrc, Cmm);
        }
        //
        public void Delte_TblPhieutienan_DelByNamhocId(int NamhocId)
        {
            string NamePrc = "delete TblPhieutienan            where (Id in (SELECT TblPhieutienan.Id  FROM         TblPhieutienan INNER JOIN TblNamtrehoc ON TblPhieutienan.NamtrehocId = TblNamtrehoc.Id  WHERE     (TblNamtrehoc.NamhocId = " + NamhocId.ToString() + "))) ";
            OleDbCommand Cmm = new OleDbCommand();
            ClsConnection.OleDbDAdpter(NamePrc, Cmm);
        }
        public void Delte(int Id)
        {
            string NamePrc = QueryString.SPROC_TBLPHIEUTIENAN_DEL.Replace("@Id", Id.ToString());
            OleDbCommand Cmm = new OleDbCommand();

            ClsConnection.OleDbDAdpter(NamePrc, Cmm);
        }
        public DataTable GetTongsobuaan(DateTime Ngaythang, int NamtrehocId)
        {
            DataTable Tbl = new DataTable();


            string Sql = "SELECT Count([tblDiemdanhtrean].[Id]) AS Tongsobuaan FROM TblDiemdanhtrean WHERE (((TblDiemdanhtrean.NamtrehocId)=" + NamtrehocId + ") AND ((TblDiemdanhtrean.Trangthai)=-1) AND ((Month([TblDiemdanhtrean].[Ngay]))=" + Ngaythang.Month + ") AND ((Year([TblDiemdanhtrean].[ngay]))=" + Ngaythang.Year + "))";
            OleDbCommand Cmm = new OleDbCommand();
            ClsConnection.OleDbDAdpter(Sql, Cmm).Fill(Tbl);



            return Tbl;
        }
        public static DataTable GetAllByLop(int LophocID, int ThanghocID)
        {
            QueryString.SP_VWPHIEUTIENAN_SELECTBYLOPHOC = "SELECT * FROM vwPhieutienan WHERE ThanghocID = " + ThanghocID;
            if (LophocID != 0)
                QueryString.SP_VWPHIEUTIENAN_SELECTBYLOPHOC += " and LophocId = " + LophocID;
            string NamePrc = QueryString.SP_VWPHIEUTIENAN_SELECTBYLOPHOC;

            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();
            string NameTable = "TBLTREPHAITHUTHANG";
            NewTable.TableName = NameTable;

            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            Dset.Tables.Add(NewTable);

            OleDbCommand Cmm = new OleDbCommand();
            ClsConnection.OleDbDAdpter(NamePrc, Cmm).Fill(Dset.Tables[NameTable]);
            return Dset.Tables[NameTable];
            //return QLMN.Common.DataAccess.FillTable(ClsConnection.connectString, "SP_vwPhieutienan_SelectByLophoc", value, true);
        }

        public static DataTable GetAllByLop(object[] value)
        {
            return DataAccess.FillTable(ClsConnection.connectString, "SP_vwPhieutienan_SelectByLophoc", value, true);
        }
    }
}
